package com.qingke.jdbc.connection;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;

public class MyJdbcConnection {

	public static void main(String[] args) {
		loadDriverClass();
		System.out.println("====== Statement ======");
		doStatement();
		System.out.println("====== PreparedStatement ======");
		doPreparedStatement();	
		System.out.println("====== CallableStatement ======");
		doCallableStatement();
	}
	
	private static void doStatement() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
		
			conn = getConnection();
			
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from qingke.student");
			
			while (rs.next()) {
				int id = rs.getInt("id");
				String lastname = rs.getString("lastname");
				String firsttname = rs.getString("firstname");
				System.out.println("id: " + id 
						+ ", name: " + lastname.concat(firsttname));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
	}
	
	private static void doPreparedStatement() {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stmt = conn.prepareStatement("select * from qingke.student where id = ?");
			
			stmt.setInt(1, 1);
			
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				int id = rs.getInt("id");
				String lastname = rs.getString("lastname");
				String firsttname = rs.getString("firstname");
				System.out.println("id: " + id 
						+ ", name: " + lastname.concat(firsttname));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clean(conn, stmt, rs);
		}
	}
	
	private static void doCallableStatement() {
		Connection conn = null;
		CallableStatement stmt = null;
		ResultSet rs = null;
		Scanner sc = new Scanner(System.in);
		System.out.println("major partern:");
		String majorCode = sc.nextLine();
		System.out.println("academyYear partern:");
		String academyYear = sc.nextLine();
		
		try {
			conn = getConnection();
			stmt = conn.prepareCall("call qingke.sp_course_impl_list_by_major(?, ?)");
			
			stmt.setString(1, majorCode);
			stmt.setString(2, academyYear);
			
			stmt.execute();
			
			rs = stmt.getResultSet();
			
			while (rs.next()) {
				String code = rs.getString("code");
				String name = rs.getString("name");
				String teacherName = rs.getString("teacher_name");
				String remain = rs.getString("remain");
				String capacity = rs.getString("capacity");
				Date date = rs.getDate("create_time");
				System.out.println("code: " + code 
						+ ", name: " + name 
						+ ", teacherName: " + teacherName
						+ ", remain: " + remain
						+ ", capacity: " + capacity
						+ ", date: " + date.toString());
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sc.close();
			clean(conn, stmt, rs);
		}
	}

	public static void clean(Connection conn, Statement stmt, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static Connection getConnection() {
		Connection conn = null;
		String url = getProperty("url");
		String user = getProperty("user");
		String password = getProperty("password");
		
		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	private static void loadDriverClass() {
		try {
			Class.forName(getProperty("jdbc.driver"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	private static String getProperty(String key) {
		Properties property = new Properties();
		try {
			property.load(new FileInputStream("conn.properties"));
			return property.getProperty(key);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return "";
	}

}
